/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package giormi;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author Ling
 */
public class F_updatestock {
    private String s_dateposted;
    private String s_datereceived;
    private String s_quantity;
    private String s_pquantity;
    private int s_balance;
    private int s_iquantity;
    private int s_ibalance;
    private String s_rrnum;
    private String s_quedan;
    private String s_expdate;
    private String s_proddate;
    private String s_lotnum;
    private String s_notes;
    private int s_idproduct;
    private int idstock;
    
    private String s_productname;
    private String s_productpacking;

    public void setS_dateposted(String s_dateposted) {
        this.s_dateposted = s_dateposted;
    }

    public void setS_datereceived(String s_datereceived) {
        this.s_datereceived = s_datereceived;
    }

    public void setS_expdate(String s_expdate) {
        this.s_expdate = s_expdate;
    }

    public void setS_idproduct(int s_idproduct) {
        this.s_idproduct = s_idproduct;
    }

    public void setS_lotnum(String s_lotnum) {
        this.s_lotnum = s_lotnum;
    }

    public void setS_notes(String s_notes) {
        this.s_notes = s_notes;
    }

    public void setS_proddate(String s_proddate) {
        this.s_proddate = s_proddate;
    }

    public void setS_quantity(String s_quantity) {
        this.s_quantity = s_quantity;
    }

    public void setS_quedan(String s_quedan) {
        this.s_quedan = s_quedan;
    }

    public void setS_rrnum(String s_rrnum) {
        this.s_rrnum = s_rrnum;
    }
    
    //GETTERS
    public String getS_dateposted() {
        return s_dateposted;
    }

    public String getS_datereceived() {
        return s_datereceived;
    }

    public String getS_expdate() {
        return s_expdate;
    }

    public int getS_idproduct() {
        return s_idproduct;
    }

    public String getS_lotnum() {
        return s_lotnum;
    }

    public String getS_notes() {
        return s_notes;
    }

    public String getS_proddate() {
        return s_proddate;
    }

    public String getS_quantity() {
        return s_quantity;
    }

    public String getS_quedan() {
        return s_quedan;
    }

    public String getS_rrnum() {
        return s_rrnum;
    }


    private Connection conn;
    private String SQLStatement;
    private String URL;
    private Statement stmt;
    public ResultSet rset;

    public void connect(){
        try{
            DBConnection db = new DBConnection();
            conn = DBConnection.get();
            stmt = conn.createStatement();
        }
        catch(Exception e){
            System.out.println(e.getMessage());
            JOptionPane.showMessageDialog(null,e.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public void disconnect(){
        try{
            stmt.close();
            conn.close();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

    public int getidproduct(String productname, String packing){
        int idproduct = 0;

        connect();
        try {
            SQLStatement = "SELECT idproduct FROM product "
                    + "WHERE p_name='"+productname+"' AND p_packing='"+packing+"' AND p_status ='Active'";
            rset = stmt.executeQuery(SQLStatement);
            if (rset.next()) {
              idproduct = rset.getInt(1);
              System.out.println(rset.getInt(1));
            }
            rset.close();
            disconnect();
        } catch (SQLException ex) {
            Logger.getLogger(F_updatestock.class.getName()).log(Level.SEVERE, null, ex);
        }
        return idproduct;
    }
    
    public void getstock(int idstock){
        this.idstock = idstock;
        connect();
        try{
            SQLStatement = "SELECT * FROM stock WHERE idstock = " +idstock;
            rset = stmt.executeQuery(SQLStatement);
            if(rset.next() != false){
                idstock = rset.getInt(1);
                this.s_dateposted = rset.getString(2);
                this.s_datereceived = rset.getString(3);
                this.s_quantity = rset.getString(4);
                this.s_balance = rset.getInt(5);
                this.s_rrnum= rset.getString(6);
                this.s_quedan = rset.getString(7);
                this.s_expdate = rset.getString(8);
                this.s_proddate = rset.getString(9);
                this.s_lotnum = rset.getString(10);
                this.s_notes = rset.getString(11);
                this.s_idproduct = rset.getInt(12);                
            }
            rset.close();
            disconnect();
            s_pquantity = s_quantity;
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }
    
    public String getproduct(String type){
        String temp;
        connect();
        try{
            SQLStatement = "SELECT * FROM product WHERE idproduct = " + s_idproduct;
            rset = stmt.executeQuery(SQLStatement);
            if(rset.next() != false){
                s_idproduct = rset.getInt(1);
                s_productname = rset.getString(4);
                s_productpacking = rset.getString(5);       
            }
            rset.close();
            disconnect();
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
        if ("name".equals(type))
            temp = s_productname;
        else
            temp = s_productpacking;
        System.out.println("productname:"+temp);
        return temp;
    }
    
    public void updatestock(){
        s_iquantity = Integer.parseInt(s_pquantity); //prev qty
        s_iquantity = Integer.parseInt(s_quantity) - s_iquantity; // updated - prev
        s_ibalance = s_balance + s_iquantity;

        try{
           SQLStatement = "UPDATE stock SET "
                   + "s_dateposted = '" + s_dateposted +
                   "',s_datereceived = '" + s_datereceived +
                   "',s_quantity = " + Integer.parseInt(s_quantity) +
                   ",s_rrnum = '" + s_rrnum +
                   "',s_quedan = '" + s_quedan + 
                   "',s_lotnum = '" + s_lotnum + 
                   "',s_proddate = '" + s_proddate + 
                   "',s_expdate = '" + s_expdate +
                   "',s_notes = '" + s_notes +
                   "',s_idproduct = " + s_idproduct +
                   ",s_balance = " + s_ibalance +
                   " WHERE idstock =" + idstock;
                   
            stmt.executeUpdate(SQLStatement);
        }
        catch(Exception ex){
            System.out.println(ex.getMessage());
            JOptionPane.showMessageDialog(null,ex.getMessage(),"ERROR", JOptionPane.WARNING_MESSAGE);
        }
    }

}
